package com.lanyou.esb.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import com.lanyou.esb.entity.IfAlarm;

/**
 * 服务告警统计查询
 * 
 * @author Davey.wu
 */
public interface ServiceAlarmStatisticsRepository extends
		Repository<IfAlarm, String>, JpaSpecificationExecutor<IfAlarm> {
	/**
	 * 获取告警信息总数
	 * 
	 * @param fieldId
	 * @param systemId
	 * @param ifName
	 * @param alarmType
	 * @param startDate
	 * @param endDate
	 * @return
	 */

	@Query(nativeQuery = true, value = "SELECT "
			+ "COUNT(V1.IF_ID) AS total "
			+ "FROM "
			+ "	(SELECT "
			+ "	V0.IF_ID, "
			+ "	V0.IF_NAME, "
			+ "	V0.FIELD_SYSTEM_ID, "
			+ "	V0.ALARM_TYPE, "
			+ "	SUM(V0.times) AS times "
			+ "	FROM "
			+ "		(SELECT  "
			+ "		a0.IF_ID, "
			+ "		a0.IF_NAME, "
			+ "		a0.FIELD_SYSTEM_ID, "
			+ "		a0.ALARM_TYPE, "
			+ "		a0.IF_ALARM_REPEAT times "
			+ "		FROM t_esb_bu_if_alarm a0 "
			+ "		WHERE  "
			+ "		(''=:alarmType OR a0.ALARM_TYPE =:alarmType) AND "
			+ "		(''=:ifName OR a0.IF_NAME LIKE :ifName) AND "
			+ "		(''=:systemId OR a0.FIELD_SYSTEM_ID=:systemId) AND "
			+ "		(''=:fieldId OR EXISTS (SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=:fieldId)) AND "
			+ "		(''=:startDate OR a0.IN_ESB_DATE>=:startDate) AND "
			+ "		(''=:endDate OR a0.IN_ESB_DATE<=:endDate) "
			+ "		UNION ALL "
			+ "		SELECT  "
			+ "		a0.IF_ID, "
			+ "		a0.IF_NAME, "
			+ "		a0.FIELD_SYSTEM_ID, "
			+ "		a0.ALARM_TYPE, "
			+ "		a0.IF_ALARM_REPEAT times "
			+ "		FROM t_esb_bu_if_alarm_his a0 "
			+ "		WHERE  "
			+ "		(''=:alarmType OR a0.ALARM_TYPE =:alarmType) AND "
			+ "		(''=:ifName OR a0.IF_NAME LIKE :ifName) AND "
			+ "		(''=:systemId OR a0.FIELD_SYSTEM_ID=:systemId) AND "
			+ "		(''=:fieldId OR EXISTS (SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=:fieldId)) AND "
			+ "		(''=:startDate OR a0.IN_ESB_DATE>=:startDate) AND "
			+ "		(''=:endDate OR a0.IN_ESB_DATE<=:endDate) " + "		)AS V0  "
			+ "	GROUP BY V0.IF_ID,V0.ALARM_TYPE "
			+ "	) AS V1")
	List<Object> findServiceAlarmStatisticsTotal(
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId, @Param("ifName") String ifName,
			@Param("alarmType") String alarmType,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	/**
	 * 获取告警信息，分页
	 * 
	 * @param fieldId
	 * @param systemId
	 * @param ifName
	 * @param alarmType
	 * @param startDate
	 * @param endDate
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT "
			+ "(SELECT f.FIELD_NAME FROM t_cook_bu_field_system s,t_cook_db_field f WHERE s.FIELD_SYSTEM_ID=V1.FIELD_SYSTEM_ID AND s.FIELD_ID=f.FIELD_ID) AS fieldName, "
			+ "(SELECT s.SYSTEM_NAME FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=V1.FIELD_SYSTEM_ID) AS systemName, "
			+ "V1.IF_NAME AS ifName, "
			+ "(SELECT h.IF_STATUS FROM t_esb_bu_if_http h WHERE h.IF_HTTP_ID=V1.IF_ID) AS ifStatus, "
			+ "V1.ALARM_TYPE, "
			+ "V1.times "
			+ "FROM "
			+ "	(SELECT "
			+ "	V0.IF_ID, "
			+ "	V0.IF_NAME, "
			+ "	V0.FIELD_SYSTEM_ID, "
			+ "	V0.ALARM_TYPE, "
			+ "	SUM(V0.times) AS times "
			+ "	FROM "
			+ "		(SELECT  "
			+ "		a0.IF_ID, "
			+ "		a0.IF_NAME, "
			+ "		a0.FIELD_SYSTEM_ID, "
			+ "		a0.ALARM_TYPE, "
			+ "		a0.IF_ALARM_REPEAT times "
			+ "		FROM t_esb_bu_if_alarm a0 "
			+ "		WHERE  "
			+ "		(''=:alarmType OR a0.ALARM_TYPE =:alarmType) AND "
			+ "		(''=:ifName OR a0.IF_NAME LIKE :ifName) AND "
			+ "		(''=:systemId OR a0.FIELD_SYSTEM_ID=:systemId) AND "
			+ "		(''=:fieldId OR EXISTS (SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=:fieldId)) AND "
			+ "		(''=:startDate OR a0.IN_ESB_DATE>=:startDate) AND "
			+ "		(''=:endDate OR a0.IN_ESB_DATE<=:endDate) "
			+ "		UNION ALL "
			+ "		SELECT  "
			+ "		a0.IF_ID, "
			+ "		a0.IF_NAME, "
			+ "		a0.FIELD_SYSTEM_ID, "
			+ "		a0.ALARM_TYPE, "
			+ "		a0.IF_ALARM_REPEAT times "
			+ "		FROM t_esb_bu_if_alarm_his a0 "
			+ "		WHERE  "
			+ "		(''=:alarmType OR a0.ALARM_TYPE =:alarmType) AND "
			+ "		(''=:ifName OR a0.IF_NAME LIKE :ifName) AND "
			+ "		(''=:systemId OR a0.FIELD_SYSTEM_ID=:systemId) AND "
			+ "		(''=:fieldId OR EXISTS (SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=:fieldId)) AND "
			+ "		(''=:startDate OR a0.IN_ESB_DATE>=:startDate) AND "
			+ "		(''=:endDate OR a0.IN_ESB_DATE<=:endDate) " + "		)AS V0  "
			+ "	GROUP BY V0.IF_ID,V0.ALARM_TYPE LIMIT :pageNumber,:pageSize "
			+ "	) AS V1")
	List<Object[]> findServiceAlarmStatisticsInfo(
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId, @Param("ifName") String ifName,
			@Param("alarmType") String alarmType,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	/**
	 * 获取告警信息，不分页
	 * 
	 * @param fieldId
	 * @param systemId
	 * @param ifName
	 * @param alarmType
	 * @param startDate
	 * @param endDate
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT "
			+ "(SELECT f.FIELD_NAME FROM t_cook_bu_field_system s,t_cook_db_field f WHERE s.FIELD_SYSTEM_ID=V1.FIELD_SYSTEM_ID AND s.FIELD_ID=f.FIELD_ID) AS fieldName, "
			+ "(SELECT s.SYSTEM_NAME FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=V1.FIELD_SYSTEM_ID) AS systemName, "
			+ "V1.IF_NAME AS ifName, "
			+ "(SELECT h.IF_STATUS FROM t_esb_bu_if_http h WHERE h.IF_HTTP_ID=V1.IF_ID) AS ifStatus, "
			+ "V1.ALARM_TYPE, "
			+ "V1.times "
			+ "FROM "
			+ "	(SELECT "
			+ "	V0.IF_ID, "
			+ "	V0.IF_NAME, "
			+ "	V0.FIELD_SYSTEM_ID, "
			+ "	V0.ALARM_TYPE, "
			+ "	SUM(V0.times) AS times "
			+ "	FROM "
			+ "		(SELECT  "
			+ "		a0.IF_ID, "
			+ "		a0.IF_NAME, "
			+ "		a0.FIELD_SYSTEM_ID, "
			+ "		a0.ALARM_TYPE, "
			+ "		a0.IF_ALARM_REPEAT times"
			+ "		FROM t_esb_bu_if_alarm a0 "
			+ "		WHERE  "
			+ "		(''=:alarmType OR a0.ALARM_TYPE =:alarmType) AND "
			+ "		(''=:ifName OR a0.IF_NAME LIKE :ifName) AND "
			+ "		(''=:systemId OR a0.FIELD_SYSTEM_ID=:systemId) AND "
			+ "		(''=:fieldId OR EXISTS (SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=:fieldId)) AND "
			+ "		(''=:startDate OR a0.IN_ESB_DATE>=:startDate) AND "
			+ "		(''=:endDate OR a0.IN_ESB_DATE<=:endDate) "
			+ "		UNION ALL "
			+ "		SELECT  "
			+ "		a0.IF_ID, "
			+ "		a0.IF_NAME, "
			+ "		a0.FIELD_SYSTEM_ID, "
			+ "		a0.ALARM_TYPE, "
			+ "		a0.IF_ALARM_REPEAT times"
			+ "		FROM t_esb_bu_if_alarm_his a0 "
			+ "		WHERE  "
			+ "		(''=:alarmType OR a0.ALARM_TYPE =:alarmType) AND "
			+ "		(''=:ifName OR a0.IF_NAME LIKE '%:ifName%') AND "
			+ "		(''=:systemId OR a0.FIELD_SYSTEM_ID=:systemId) AND "
			+ "		(''=:fieldId OR EXISTS (SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=a0.FIELD_SYSTEM_ID AND s.FIELD_ID=:fieldId)) AND "
			+ "		(''=:startDate OR a0.IN_ESB_DATE>=:startDate) AND "
			+ "		(''=:endDate OR a0.IN_ESB_DATE<=:endDate) " + "		)AS V0  "
			+ "	GROUP BY V0.IF_ID,V0.ALARM_TYPE " + "	) AS V1")
	List<Object[]> findServiceAlarmStatisticsInfo(
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId, @Param("ifName") String ifName,
			@Param("alarmType") String alarmType,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);
}
